#!/usr/bin/perl

use strict ; 

use DBI ;
use Mysql ;

my $host = 'localhost';
my $database = 'plan_slb';
my $user = 'planslb';
my $password = 'slb_uol';


#my @urls = qw ( 'http://192.168.146.63/logs/xerife12.txt' 'http://192.168.146.63/logs/xerife11.txt' );
my @urls = qw ( 'http://192.168.146.63/logs/xerife12.txt' 'http://192.168.146.63/logs/xerife11.txt' 'http://192.168.146.63/logs/check_vip_foundry.txt' );


my %switches = ();
my %vips = ();
my %hosts = ();
my %farms = ();

sub getHostName ( $ )
{
   my $ip = shift();
   
   my $host_controle  = "localhost";
   my $db_controle = "servidor";
   my $user_controle = "controle";
   my $pass_controle = "c0ntr0l3";

   my $dbh_controle = DBI->connect("DBI:mysql:database=$db_controle;host=$host_controle",$user_controle,$pass_controle,{'RaiseError' => 1});

   my $sth_controle = $dbh_controle->prepare("select s.nome from servidor as s, ip as i  where i.ip = \"$ip\" and i.id_servidor = s.id_servidor " );
   $sth_controle->execute;
   my $ref = $sth_controle->fetchrow(); 

   $sth_controle->finish();
   
   $dbh_controle->disconnect();
   if ( $ref eq "" )
   {
      $ref = "UNKNOWN" ;
   }
   else
   {
      $ref = uc ( $ref );
   }
   
   
   return ( $ref );
}


sub createHash ( @ )# Read Files and split information 
{
    my @array = @_ ;

    
    foreach my $line ( @array )
    {

        if ( $line =~ /Switch:\s(.*)\sVirtualServer:\s(.*)\sVirtualIP:\s([\d|\.]+)\sRealIP:\s([\d|\.]+)\sRealName:\s(.*)\sMaxConn:\s([\d+|\:|\s]+)\sWeight:\s(\d+)/ )
        {
           my $switchin = $1 ;
           my ( $switch_name ) = split(/\./,"$switchin") ;
           my $vip_name = $2 ;
           my $vip_ip = $3 ;

           my $host_ip = $4 ;
           my $host_name = $5; 
           my $maxconn_port = $6;
           my $weight = $7;
           my $host_port = 0;
           my $maxconn = 0;
	   my $switch_type;

	   if ( $switch_name ne "" ) {
	    	$switch_type = "foundry";
		if ($switch_name eq "xerife9" || $switch_name eq "xerife10") {
		   $switch_name = "xerife9/xerife10";
		}
		elsif ($switch_name eq "xerife15" || $switch_name eq "xerife16") {
		   $switch_name = "xerife15/xerife16";
		}
		elsif ($switch_name eq "xerife17" || $switch_name eq "xerife18") {
		   $switch_name = "xerife17/xerife18";
		}
		elsif ($switch_name eq "xerife19" || $switch_name eq "xerife20") {
		   $switch_name = "xerife19/xerife20";
		}
	   }
	   else {
		$switch_name = "xerife11/xerife12";
	    	$switch_type = "csm";
	   }

           next if ( $maxconn_port =~ /^0/ ) ;

           my ( @maxconns ) = split ( /\s/, $maxconn_port );

           my $vip_name_aux = $vip_name ;

           foreach my $reg ( @maxconns )
           {
               if ( $reg =~ /(\d+):(\d+)/ )
               {
                   $host_port = $1 ;
                   $maxconn = $2 ;
               }

               next if ( $host_port == 65535 );
           
               if ( $host_port != 80 )
               {
                  $vip_name = "${vip_name_aux}_$host_port" ;
               }
    
               my $host = "$vip_name:$host_ip" ;
    
               if ( !exists $vips{$vip_name} )
               {
                 $vips{$vip_name} = $vip_ip ;
               }
    
               if ( !exists $farms{$vip_name} )
               {
                 $farms{$vip_name} = $vip_ip ;
               }
    
               if ( !exists $hosts{$host} )
               {
                 $hosts{$host} = "$maxconn:$weight:$switch_name" ;
               }
               
	       my $switch = "$switch_name:$switch_type" ;

               if ( !exists $switches{$switch} )
               {
                 $switches{$switch} = "$switch_name:$switch_type" ;
               }

           }
        }
        elsif ( $line =~ /VIP\s([\w|\d]+)\s.*ip\s([\d|\.]+)/ )
        {

           my $name = $1 ;
           my $ip = $2 ;
           if ( !exists $vips{$name} )
           {
             $vips{$name} = $ip ;
           } 
              
           #print "vip $1 $2\n" ; 
        }
        elsif ( $line =~ /HOST\s([\w|\d]+)\(.*id\(\d+\)\s([\d|\.]+)\smax_conn\s(\d+)/ )
        {
           my $hostname = $1 ;
           my $ip = $2 ;
           my $host = "$hostname:$ip" ;
           my $maxconn = $3 ;
    
           if ( !exists $hosts{$host} )
           {
             $hosts{$host} = "$maxconn" ;
           }
           #print "host $1 $2 $3 $4\n" ; 
        }
        elsif ( $line =~ /HOST\s([\w|\d]+)\(.*id\(\d+\)\s([\d|\.]+)\soper_weight\s(\d+)/ )
        {
           my $hostname = $1 ;
           my $ip = $2 ;
           my $host = "$hostname:$ip" ;
           my $oper_weight = $3 ;

           if ( exists $hosts{$host} )
           {
             $hosts{$host} = $hosts{$host} . ":$oper_weight" ;
           }
           #print "host $1 $2 $3 $4\n" ;
        }
        elsif ( $line =~ /^FARM\s([\w|\d|\_|\-]+).*/ )
        { 
            my $farm = $1 ;
            if ( !exists $farms{$farm} )
            {
                $farms{$farm} = 0 ;
            }  
        } 
    }
}



foreach my $url ( @urls )
{
    my @output = `wget --http-user=nagiosadmin --http-passwd=nagios123  $url -O /dev/stdout`;
    &createHash(@output );
}



my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host",$user,$password,{'RaiseError' => 1});

my $sth = $dbh->do("truncate table switch");
$sth = $dbh->do( "INSERT INTO switch (switch_name, switch_type) values (\'xerife11/xerife12\',\'csm')" );

foreach my $switch ( sort keys %switches )
{
   my ( $switchname, $switchtype ) = split ( /:/, $switch );
   #print "switch:$switchname:$switchtype\n";

   my $sth = $dbh->prepare("select switch_id from switch where switch_name =\"$switch\" ") ;
   $sth->execute();
   my $result = $sth->fetchrow();
   $sth->finish();

   if ( $result eq "" )
   {
       $sth = $dbh->do( "INSERT INTO switch (switch_name, switch_type) values (\'$switchname\',\'$switchtype\')" );
   }
}


my $vipsfile="/tmp/vipsfile";
my $sth = $dbh->prepare("select vip_ip, vip_image from vips");
$sth->execute();
open (VIPSFILE, ">$vipsfile");
while (my $ref = $sth->fetchrow_hashref()) {
                my $vip_ip = $ref->{'vip_ip'};
                my $vip_image = $ref->{'vip_image'};
		print VIPSFILE "$vip_ip|$vip_image\n";
}
$sth->finish();

my $sth = $dbh->do("truncate table vips");

foreach my $vip ( sort keys %vips )
{
   #print "VIP:$vip:$vips{$vip}\n";
   
   my $sth = $dbh->prepare("select vip_id from vips where vip_name =\"$vip\" ") ;
   $sth->execute();
   my $result = $sth->fetchrow();
   $sth->finish();

   if ( $result eq "" )
   {
       $sth = $dbh->do( "INSERT INTO vips (vip_name, vip_ip ) values (\'$vip\',\'$vips{$vip}\')" ); 
   }  
}

open (VIPSFILE, "$vipsfile");
foreach (<VIPSFILE>) {
   chop;
   my ($vip_ip, $vip_image) = split (/\|/,"$_");
   $sth = $dbh->do( "UPDATE vips SET vip_image=\"$vip_image\" WHERE vip_ip=\"$vip_ip\"" ); 
   #print "$vip_ip | $vip_image\n";
}

foreach my $host ( sort keys %hosts )
{
   my ( $farmname , $serverip ) = split ( /:/, $host );
   my $hostname = getHostName($serverip);
   #print "HOST:$farmname:$serverip:$hostname:$hosts{$host}\n";

   my $sth = $dbh->prepare("select server_name from servers where server_ip =\"$serverip\" ") ;
   $sth->execute();
   my $result = $sth->fetchrow();
   $sth->finish();

   if ( $result eq "" )
   {
       $sth = $dbh->do( "INSERT INTO servers (server_name, server_ip ) values (\'$hostname\',\'$serverip\')" );
   }
   elsif ( $result eq "UNKNOWN"  and $hostname ne "UNKNOWN" ) 
   {
       $sth = $dbh->do( "update servers set server_name=\'$hostname\' where server_ip=\'$serverip\'" );
   }

   #my $out = `mysql -uplanslb -pslb_uol plan_slb -e \"insert into farms (farm_name ) values (\'$farmname\')\"`;  
}

my $sth = $dbh->do("truncate table farms");

foreach my $farm ( sort keys %farms )
{
   #print "FARM:$farm\n";

   my $sth = $dbh->prepare("select farm_id from farms where farm_name =\"$farm\" ") ;
   $sth->execute();
   my $result = $sth->fetchrow();
   $sth->finish();

   if ( $result eq "" )
   {
       $sth = $dbh->do( "INSERT INTO farms (farm_name) values (\'$farm\')" );
   }
}

my $sth = $dbh->do("truncate table farmserver");

foreach my $host ( sort keys %hosts )
{
   my ( $farmname , $serverip ) = split ( /:/, $host );
   my ( $maxconn, $weight, $switchname ) = split ( /:/, $hosts{$host} ); 
   if ($switchname eq "") {
     $switchname = "xerife11/xerife12";
   }

   my $sth = $dbh->prepare("select fs_server_id from farmserver servers  where fs_farm_id = ( select farm_id from farms where farm_name = \"$farmname\" ) and fs_server_id = (select server_id from servers where server_ip = \"$serverip\") and  fs_switch_id = ( select switch_id from switch where switch_name = \"$switchname\" ); ") ;

   $sth->execute();
   my $result = $sth->fetchrow();
   $sth->finish();

   my $sth = $dbh->prepare("select farm_id from farms where farm_name =\"$farmname\" ") ;
   $sth->execute();
   my $resultfarm = $sth->fetchrow();
   $sth->finish();

   if ( $result eq "" )
   {
       if ( ! $resultfarm eq "" ) 
       {
          #print "$farmname , $serverip\n";
          $sth = $dbh->do( "INSERT INTO farmserver (fs_farm_id, fs_server_id, maxconn, weight, fs_switch_id ) values ( (select farm_id from farms where farm_name = \'$farmname\'), (select server_id from servers where server_ip = \'$serverip\'), \"$maxconn\" , \"$weight\", (select switch_id from switch where switch_name = \'$switchname\') )" );
       }
   }
}
